package com.dy.yunying.biz.dao.hbdataclickhouse.impl;

import cn.hutool.core.collection.CollectionUtil;
import com.dy.yunying.api.req.sign.SignActivityDataReq;
import com.dy.yunying.api.resp.SignActivityDataRes;
import com.google.common.collect.Lists;
import com.pig4cloud.pig.api.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author chenxiang
 * @className SignActiveDataDao
 * @date 2021/12/2 20:56
 */
@Slf4j
@Component
public class SignActiveDataDao {

	@Value(value = "${hbdxtable}")
	private String hbDxTable;

	@Resource(name = "hbdataclickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	/**
	 * 签到活动数据
	 * @param req
	 * @return
	 */
	public List<SignActivityDataRes> signActivityDataList(SignActivityDataReq req, Set<String> activityIds){
		Map<String,Object> result = new HashMap<>();
		String sql = dealSql(req, activityIds);
		sql += " order by t.date desc ";
		log.info(">>>签到活动数据sql:{}",sql);
		return clickhouseTemplate.query(sql, new Object[]{},new BeanPropertyRowMapper<>(SignActivityDataRes.class));
	}
	/**
	 * 签到活动数据 - 汇总数据
	 * @param req
	 * @return
	 */
	public List<SignActivityDataRes> summarySignActivityDataList(SignActivityDataReq req, Set<String> activityIds){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SUM(servicePV) servicePV,SUM(serviceUV) serviceUV,SUM(serviceIP) serviceIP,SUM(bindRoleNum) bindRoleNum, ").append("\n");
		sql.append(" SUM(signRoleNum) signRoleNum,SUM(repairRoleNum) repairRoleNum,SUM(doneTaskNum) doneTaskNum, ").append("\n");
		sql.append(" SUM(signTotal) signTotal ").append("\n");
		sql.append(" from ( ").append("\n");
		sql.append(summaryDealSql(req, activityIds)).append("\n");
		sql.append(" ) t ").append("\n");
		log.info(">>>签到活动数据summary sql:{}",sql);
		return clickhouseTemplate.query(sql.toString(), new Object[]{},new BeanPropertyRowMapper<>(SignActivityDataRes.class));
	}
	public String dealSql(SignActivityDataReq req, Set<String> activityIds){
		StringBuffer sql = new StringBuffer();
		// 数据统计sql
		sql.append("\n").append(" SELECT ").append("\n");
		sql.append(" 	 t.date date,  ").append("\n");
		sql.append(" 	 t.activityId activityId,  ").append("\n");
		sql.append(" 	 t.servicePV servicePV,  ").append("\n");
		sql.append(" 	 t.serviceUV serviceUV,  ").append("\n");
		sql.append(" 	 t.serviceIP serviceIP,  ").append("\n");
		sql.append(" 	 t.bindRoleNum bindRoleNum,  ").append("\n");
		sql.append(" 	 t.signRoleNum signRoleNum,  ").append("\n");
		sql.append(" 	 t.repairRoleNum repairRoleNum, ").append("\n");
		sql.append(" 	 t.signTotal signTotal, ").append("\n");
		sql.append(" 	 a.doneTaskNum doneTaskNum ").append("\n");
		sql.append(" FROM ( ").append("\n");
		sql.append(" 	SELECT  ").append("\n");
		sql.append(" 		 t.date date ,  ").append("\n");
		sql.append(" 		 t.activityId activityId,  ").append("\n");
		sql.append(" 		 t.servicePV servicePV,  ").append("\n");
		sql.append(" 		 t.serviceUV serviceUV,  ").append("\n");
		sql.append(" 		 t.serviceIP serviceIP,  ").append("\n");
		sql.append(" 		 t.bindRoleNum bindRoleNum,  ").append("\n");
		sql.append(" 		 a.signTotal signTotal, ").append("\n");
		sql.append(" 		 a.signRoleNum signRoleNum,  ").append("\n");
		sql.append(" 		 a.repairRoleNum repairRoleNum ").append("\n");
		sql.append(" 	from ( ").append("\n");
		sql.append(" 		SELECT  ").append("\n");
		sql.append(" 			t.date date ,  ").append("\n");
		sql.append(" 			 t.activityId activityId,  ").append("\n");
		sql.append(" 			 t.servicePV servicePV,  ").append("\n");
		sql.append(" 			 t.serviceUV serviceUV,  ").append("\n");
		sql.append(" 			 t.serviceIP serviceIP,  ").append("\n");
		sql.append(" 			 a.bindRoleNum bindRoleNum  -- 绑定角色数 ").append("\n");
		sql.append(" 		from ( ").append("\n");
		sql.append("  			SELECT t.dx_part_date date ,   ").append("\n");
		sql.append("  				 t.activity_id activityId,   ").append("\n");
		sql.append("  				 count(*) servicePV, -- pv  ").append("\n");
		sql.append("  				 count(DISTINCT t.roleid) serviceUV,  --uv  ").append("\n");
		sql.append("  				 count(DISTINCT t.dx_ip) serviceIP -- ip  ").append("\n");
		sql.append("  			 FROM (select biz_id activity_id,roleid roleid,dx_ip dx_ip,dx_event_name dx_event_name,place place,dx_part_date dx_part_date FROM "+hbDxTable+" ) t   ").append("\n");
		sql.append("  			 where t.dx_event_name = 'page_view'  ").append("\n");
		sql.append("  			 AND t.place = 'signIn_index'  ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append("  			 group by t.dx_part_date,t.activity_id ").append("\n");
		sql.append(" 		) t ").append("\n");
		sql.append(" 		FULL JOIN ( ").append("\n");
		sql.append(" 			-- 有多少个角色参与了签到活动 ").append("\n");
		sql.append(" 			SELECT  ").append("\n");
		sql.append(" 				dx_part_date date,activity_id activityId, ").append("\n");
		sql.append(" 				COUNT(DISTINCT roleid) bindRoleNum  -- 绑定角色数 ").append("\n");
		sql.append(" 			FROM "+hbDxTable+"  ").append("\n");
		sql.append(" 			where dx_event_name = 'sign_bound_role' ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append(" 			group by dx_part_date,activity_id ").append("\n");
		sql.append(" 		) a USING (date,activityId) ").append("\n");
		sql.append(" 	) t ").append("\n");
		sql.append(" 	FULL JOIN ( ").append("\n");
		sql.append(" 		-- 有多少个角色完成了签到行为   有多少个角色完成了补签行为 ").append("\n");
		sql.append(" 		SELECT ").append("\n");
		sql.append(" 			t.date date,t.activityId activityId, ").append("\n");
		sql.append(" 			t.signRoleTotal signRoleTotal,  -- 签到总角色数 ").append("\n");
		sql.append(" 			t.signRoleNum signRoleNum,  -- 签到角色数 ").append("\n");
		sql.append(" 			t.repairRoleNum repairRoleNum, -- 补签角色数 ").append("\n");
		sql.append(" 			a.signTotal signTotal -- 签到总次数 ").append("\n");
		sql.append(" 		FROM ( ").append("\n");
		sql.append(" 			SELECT  ").append("\n");
		sql.append(" 				t.date date,t.activityId activityId, ").append("\n");
		sql.append(" 				COUNT(*) signRoleTotal,  -- 签到总角色数 ").append("\n");
		sql.append(" 				SUM(IF(t.sign_type=2,1,0)) signRoleNum,  -- 签到角色数 ").append("\n");
		sql.append(" 				SUM(IF(t.sign_type=1,1,0)) repairRoleNum -- 补签角色数 ").append("\n");
		sql.append(" 			FROM ( ").append("\n");
		sql.append(" 				SELECT  ").append("\n");
		sql.append(" 					dx_part_date date,activity_id activityId,roleid,sign_type as sign_type ").append("\n");
		sql.append(" 				FROM "+hbDxTable+"  ").append("\n");
		sql.append(" 				where dx_event_name = 'role_signed' ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append(" 				group by dx_part_date,activity_id,sign_type,roleid ").append("\n");
		sql.append(" 			) t group by t.date,t.activityId ").append("\n");
		sql.append(" 		) t ").append("\n");
		sql.append(" 		FULL JOIN ( ").append("\n");
		sql.append(" 			SELECT  ").append("\n");
		sql.append(" 				dx_part_date date,activity_id activityId, ").append("\n");
		sql.append(" 				COUNT(*) signTotal -- 签到总次数 ").append("\n");
		sql.append(" 			FROM "+hbDxTable+"  " ).append("\n");
		sql.append(" 			where dx_event_name = 'role_signed' ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append(" 			group by dx_part_date,activity_id ").append("\n");
		sql.append(" 		) a USING (date,activityId) ").append("\n");
		sql.append(" 	) a USING (date,activityId) ").append("\n");
		sql.append(" ) t ").append("\n");
		sql.append(" LEFT JOIN ( ").append("\n");
		sql.append(" 	SELECT  ").append("\n");
		sql.append(" 		t.date date,t.activityId activityId,  ").append("\n");
		sql.append(" 		COUNT(*) doneTaskNum -- 完成任务的总数  ").append("\n");
		sql.append(" 	from (  ").append("\n");
		sql.append(" 		SELECT  ").append("\n");
		sql.append(" 			dx_part_date date,activity_id activityId,roleid roleid ").append("\n");
		sql.append(" 		FROM "+hbDxTable+"  ").append("\n");
		sql.append(" 		where dx_event_name = 'sign_recharge_task_done' ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append(" 		group by dx_part_date,activity_id,roleid ").append("\n");
		sql.append(" 	) t group by t.date,t.activityId ").append("\n");
		sql.append(" ) a on t.date = a.date and t.activityId = a.activityId ").append("\n");
		sql.append(" where 1=1 ").append("\n");
		return sql.toString();
	}
	public String summaryDealSql(SignActivityDataReq req, Set<String> activityIds){
		StringBuffer sql = new StringBuffer();
		// 数据统计sql
		sql.append("\n").append(" SELECT  ").append("\n");
		sql.append("  	 t.activityId activityId,    ").append("\n");
		sql.append("  	 t.servicePV servicePV,   ").append("\n");
		sql.append("  	 t.serviceUV serviceUV,   ").append("\n");
		sql.append("  	 t.serviceIP serviceIP,   ").append("\n");
		sql.append("  	 t.bindRoleNum bindRoleNum,   ").append("\n");
		sql.append("  	 t.signRoleNum signRoleNum,   ").append("\n");
		sql.append("  	 t.repairRoleNum repairRoleNum,  ").append("\n");
		sql.append("  	 t.signTotal signTotal,  ").append("\n");
		sql.append("  	 a.doneTaskNum doneTaskNum  ").append("\n");
		sql.append("  FROM (  ").append("\n");
		sql.append("  	SELECT    ").append("\n");
		sql.append("  		 t.activityId activityId,   ").append("\n");
		sql.append("  		 t.servicePV servicePV,   ").append("\n");
		sql.append("  		 t.serviceUV serviceUV,   ").append("\n");
		sql.append("  		 t.serviceIP serviceIP,   ").append("\n");
		sql.append("  		 t.bindRoleNum bindRoleNum,   ").append("\n");
		sql.append("  		 a.signTotal signTotal,  ").append("\n");
		sql.append("  		 a.signRoleNum signRoleNum,   ").append("\n");
		sql.append("  		 a.repairRoleNum repairRoleNum  ").append("\n");
		sql.append("  	from (  ").append("\n");
		sql.append("  		SELECT   ").append("\n");
		sql.append("  			 t.activityId activityId,   ").append("\n");
		sql.append("  			 t.servicePV servicePV,   ").append("\n");
		sql.append("  			 t.serviceUV serviceUV,   ").append("\n");
		sql.append("  			 t.serviceIP serviceIP,   ").append("\n");
		sql.append("  			 a.bindRoleNum bindRoleNum  -- 绑定角色数  ").append("\n");
		sql.append("  		from (  ").append("\n");
		sql.append("  			SELECT t.activity_id activityId,    ").append("\n");
		sql.append("  				 count(*) servicePV, -- pv  ").append("\n");
		sql.append("  				 count(DISTINCT t.roleid) serviceUV,  --uv  ").append("\n");
		sql.append("  				 count(DISTINCT t.dx_ip) serviceIP -- ip  ").append("\n");
		sql.append("  			 FROM (select biz_id activity_id,roleid roleid,dx_ip dx_ip,dx_event_name dx_event_name,place place,dx_part_date dx_part_date FROM "+hbDxTable+") t   ").append("\n");
		sql.append("  			 where t.dx_event_name = 'page_view'  ").append("\n");
		sql.append("  			 AND t.place = 'signIn_index'  ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append("  			 group by t.activity_id ").append("\n");
		sql.append("  		) t  ").append("\n");
		sql.append("  		FULL JOIN (  ").append("\n");
		sql.append("  			-- 有多少个角色参与了签到活动  ").append("\n");
		sql.append("  			SELECT activity_id activityId,  ").append("\n");
		sql.append("  				COUNT(DISTINCT roleid) bindRoleNum  -- 绑定角色数  ").append("\n");
		sql.append("  			FROM "+hbDxTable).append("\n");
		sql.append("  			where dx_event_name = 'sign_bound_role'  ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append("  			group by activity_id  ").append("\n");
		sql.append("  		) a USING (activityId)  ").append("\n");
		sql.append("  	) t  ").append("\n");
		sql.append("  	FULL JOIN (  ").append("\n");
		sql.append("  		-- 有多少个角色完成了签到行为   有多少个角色完成了补签行为  ").append("\n");
		sql.append("  		SELECT t.activityId activityId,  ").append("\n");
		sql.append("  			t.signRoleTotal signRoleTotal,  -- 签到总角色数  ").append("\n");
		sql.append("  			t.signRoleNum signRoleNum,  -- 签到角色数  ").append("\n");
		sql.append("  			t.repairRoleNum repairRoleNum, -- 补签角色数  ").append("\n");
		sql.append("  			a.signTotal signTotal -- 签到总次数  ").append("\n");
		sql.append("  		FROM (  ").append("\n");
		sql.append("  			SELECT t.activityId activityId,  ").append("\n");
		sql.append("  				COUNT(*) signRoleTotal,  -- 签到总角色数  ").append("\n");
		sql.append("  				SUM(IF(t.sign_type=2,1,0)) signRoleNum,  -- 签到角色数  ").append("\n");
		sql.append("  				SUM(IF(t.sign_type=1,1,0)) repairRoleNum -- 补签角色数  ").append("\n");
		sql.append("  			FROM (  ").append("\n");
		sql.append("  				SELECT activity_id activityId,roleid,sign_type as sign_type  ").append("\n");
		sql.append("  				FROM "+hbDxTable+"  ").append("\n");
		sql.append("  				where dx_event_name = 'role_signed'  ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append("  				group by activity_id,sign_type,roleid  ").append("\n");
		sql.append("  			) t group by t.activityId  ").append("\n");
		sql.append("  		) t  ").append("\n");
		sql.append("  		FULL JOIN (  ").append("\n");
		sql.append("  			SELECT activity_id activityId,  ").append("\n");
		sql.append("  				COUNT(DISTINCT roleid) signTotal -- 签到总次数  ").append("\n");
		sql.append("  			FROM "+hbDxTable+"  ").append("\n");
		sql.append("  			where dx_event_name = 'role_signed'  ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append("  			group by activity_id  ").append("\n");
		sql.append("  		) a USING (activityId)  ").append("\n");
		sql.append("  	) a USING (activityId)  ").append("\n");
		sql.append("  ) t  ").append("\n");
		sql.append("  LEFT JOIN (  ").append("\n");
		sql.append("  	SELECT t.activityId activityId,   ").append("\n");
		sql.append("  		COUNT(*) doneTaskNum -- 完成任务的总数   ").append("\n");
		sql.append("  	from (   ").append("\n");
		sql.append("  		SELECT activity_id activityId,roleid roleid  ").append("\n");
		sql.append("  		FROM "+hbDxTable+"  ").append("\n");
		sql.append("  		where dx_event_name = 'sign_recharge_task_done'  ").append("\n");
		sql.append(whereSql(req, activityIds)).append("\n");
		sql.append("  		group by activity_id,roleid  ").append("\n");
		sql.append("  	) t group by t.activityId  ").append("\n");
		sql.append("  ) a on t.activityId = a.activityId  ").append("\n");
		sql.append("  where 1=1  ").append("\n");
		return sql.toString();
	}
	public String whereSql(SignActivityDataReq req, Set<String> activityIds){
		StringBuffer sql = new StringBuffer();
		sql.append(" AND 1=1");
		if (StringUtils.isNotBlank(req.getStartTime())){
			sql.append(" AND toYYYYMMDD(toDate(dx_part_date)) >= ").append(req.getStartTime()).append("\n");
		}
		if (StringUtils.isNotBlank(req.getEndTime())){
			sql.append(" AND toYYYYMMDD(toDate(dx_part_date)) <= ").append(req.getEndTime()).append("\n");
		}
		if (CollectionUtil.isNotEmpty(activityIds)){
			String ids = String.join(",", activityIds).replace(",","','");
			sql.append(" AND toString(activity_id) IN ('"+ids+"') ");
		}
		return sql.toString();
	}
}
